﻿Public Class frmAccount
    Dim dsAccount As New DataSet
    Dim daAccount As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub frmAccount_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        e.Cancel = True
        Me.Hide()
    End Sub

    Private Sub frmAccount_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call gSubIconShow(Me)
        Call mkCmbPart(cmbPart)
        Call mkCmbPart(cmbSearchPart)
        Call mkCmbAccGrp()
        Call gridRefresh("default")
    End Sub

    Private Sub cmbPart_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbPart.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbPart_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbPart.SelectedIndexChanged
        mkCmbAccGrp()
        cmbSearchPart.SelectedIndex = cmbPart.SelectedIndex
    End Sub

    Private Sub cmbSearchPart_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbSearchPart.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbSearchPart_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSearchPart.SelectedIndexChanged
        cmbPart.SelectedIndex = cmbSearchPart.SelectedIndex
        Call gridRefresh("")
    End Sub

    Private Sub cmbFlag_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbFlag.KeyPress
        e.Handled = True
    End Sub

    Private Sub cmbAccGroup_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles cmbAccGroup.KeyPress
        e.Handled = True
    End Sub

    Private Sub btnSearch_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        Call gridRefresh("")
    End Sub

    Private Sub btnSearchReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchReset.Click
        txtSearch.Clear()
        Call gridRefresh("")
    End Sub

    Private Sub txtSearch_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSearch.KeyPress
        If Asc(e.KeyChar) = 13 Then
            Call gridRefresh("")
        End If
    End Sub

    Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
        Call txtReset()
    End Sub

#Region "프로시져"

    Private Sub gridRefresh(ByVal initFlag As String)
        Call dbConnection()
        Dim addSql As String = ""
        Dim itemPart As String

        dsAccount = New DataSet
        If txtSearch.Text <> "" Then
            addSql = " AND A.accountNm LIKE '%" + txtSearch.Text + "%' "
        End If

        '## 계정 구분에 따라서 공통코드를 구분하여 불러옴.
        If cmbSearchPart.SelectedIndex = 0 Then
            itemPart = "0003"
            addSql = addSql + " AND A.part='IN' "
        Else
            itemPart = "0004"
            addSql = addSql + " AND A.part='OT' "
        End If
        If initFlag = "default" Then
            addSql = " AND A.flag=''"
        End If
        sql = "SELECT A.accountCd, A.accountNm as 계정명, B.itemNm as 과목, A.flag as 사용여부 " & _
            "   FROM tblAccount A LEFT JOIN (SELECT itemCd, itemNm FROM tblComCode WHERE groupCd='" + itemPart + "' AND flag='Y') B ON A.accGroup=B.itemCd " & _
            "   WHERE A.flag<>''" + addSql + " ORDER BY B.itemNm, A.flag DESC "
        'MsgBox(sql)

        Try
            daAccount = New OleDb.OleDbDataAdapter(sql, dbConn)
            daAccount.Fill(dsAccount, "Account")

            grdAccount.DataSource = dsAccount.Tables("Account")

            dbConn.Close()
            Call setGridDesign()

        Catch err As ObjectDisposedException
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub


    Private Sub setGridDesign()

        grdAccount.Columns(0).Visible = False
        grdAccount.Columns(1).Width = "300"
        grdAccount.Columns(2).Width = "120"
        grdAccount.Columns(3).Width = "120"

        With grdAccount.DefaultCellStyle
            .Alignment = DataGridViewContentAlignment.MiddleCenter

        End With
        grdAccount.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft

        gSubMkCmbFlag(cmbFlag) '## 삭제 여부 콤보박스 초기화
        cmbFlag.SelectedIndex = 0

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Call dbConnection()
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim rtnMsg As String
        Dim valAccGroup, valAccountNm, valAccountCd, valPart, valFlag As String
        Dim curDt As DateTime = Now

        valAccountNm = gFnChkVal(txtAccountNm.Text)
        valAccountCd = gFnChkVal(txtAccountCd.Text)
        valAccGroup = cmbAccGroup.SelectedValue
        valPart = cmbPart.SelectedValue
        valFlag = cmbFlag.SelectedValue

        If valAccountNm = "" Then
            MsgBox("계정을 바르게 입력해 주세요")
            txtAccountNm.Focus()
            dbConn.Close()
            Exit Sub
        End If


        If txtAccountCd.Text = "" Then
            sql = "INSERT INTO tblAccount(accountNm, accGroup, part, flag) VALUES('" + valAccountNm + "','" + valAccGroup + "','" + valPart + "','" + valFlag + "')"
            rtnMsg = "계정이 등록되었습니다"
        Else
            sql = "UPDATE tblAccount SET accGroup='" + valAccGroup + "', accountNm='" + valAccountNm + "', part='" + valPart + "', flag='" + valFlag + "' WHERE accountCd=" + txtAccountCd.Text
            rtnMsg = "계정이 수정되었습니다"
        End If

        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            MsgBox(rtnMsg)
            Call gridRefresh("")
            Call txtReset()
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    Private Sub mkCmbPart(ByVal obj As Object)
        Call dbConnection()
        sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0005' ORDER BY itemSeq"
        dsAccount = New DataSet
        daAccount = New OleDb.OleDbDataAdapter(sql, dbConn)
        daAccount.Fill(dsAccount, "cmbPart")

        dbConn.Close()
        With obj
            .DataSource = dsAccount.Tables("cmbPart")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
            .SelectedIndex = 0
        End With
    End Sub

    Private Sub mkCmbAccGrp()
        Call dbConnection()
        Dim part As String = cmbPart.SelectedValue.ToString
        If part = "IN" Then
            sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0003' AND itemCd ORDER BY itemSeq"
        Else
            sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0004' AND itemCd ORDER BY itemSeq"
        End If
        dsAccount = New DataSet
        daAccount = New OleDb.OleDbDataAdapter(sql, dbConn)
        daAccount.Fill(dsAccount, "cmbAccGroup")

        dbConn.Close()
        With cmbAccGroup
            .DataSource = dsAccount.Tables("cmbAccGroup")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
            .SelectedIndex = 0
        End With
    End Sub

    Private Sub txtReset()
        txtAccountCd.Clear()
        txtAccountNm.Clear()
        'cmbPart.SelectedIndex = 0
        'cmbAccGroup.SelectedIndex = 0
        cmbFlag.SelectedIndex = 0
    End Sub


    Private Sub grdAccount_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdAccount.CellClick
        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If IsDBNull(grdAccount.Rows(e.RowIndex).Cells(0).Value) = True Then
            Exit Sub
        End If
        sql = "SELECT accountCd, accountNm, accGroup, part, flag  FROM tblAccount WHERE accountCd=" & grdAccount.Rows(e.RowIndex).Cells(0).Value
        'MsgBox(sql)
        Call dbConnection()
        dsAccount = New DataSet
        daAccount = New OleDb.OleDbDataAdapter(sql, dbConn)
        daAccount.Fill(dsAccount, "accountInfo")

        dbConn.Close()

        txtReset()

        txtAccountCd.Text = dsAccount.Tables("accountInfo").Rows(0).Item(0)
        txtAccountNm.Text = dsAccount.Tables("accountInfo").Rows(0).Item(1)

        If IsDBNull(dsAccount.Tables("accountInfo").Rows(0).Item(2)) = False Then
            cmbAccGroup.SelectedValue = dsAccount.Tables("accountInfo").Rows(0).Item(2)
        End If
        If IsDBNull(dsAccount.Tables("accountInfo").Rows(0).Item(3)) = False Then
            cmbPart.SelectedValue = dsAccount.Tables("accountInfo").Rows(0).Item(3)
        End If

        If IsDBNull(dsAccount.Tables("accountInfo").Rows(0).Item(4)) = False Then
            cmbFlag.SelectedValue = dsAccount.Tables("accountInfo").Rows(0).Item(4)
        End If
    End Sub
#End Region
End Class